package com.infoauto.opc.gateway.controller;

import com.infoauto.opc.gateway.entity.Result;
import com.infoauto.opc.gateway.entity.ResultCode;
import com.infoauto.opc.gateway.model.gtm4.CheckTheNumberOfUnits1;
import com.infoauto.opc.gateway.model.gtm4.CheckTheNumberOfUnits2;
import com.infoauto.opc.gateway.model.gtm4.CheckTheProcess;
import com.infoauto.opc.gateway.service.CheckTheProcessService;
import com.sun.deploy.net.URLEncoder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * 检查工序
 */

@CrossOrigin
@RestController
@RequestMapping("/checkTheProcess")
public class CheckTheProcessController {
    @Autowired
    private CheckTheProcessService service;

    @RequestMapping(value="/list" ,method= RequestMethod.GET)
    public Result selectByProducTionTimeAndShift(String productionTime,Integer shift){
        try{
            List<CheckTheProcess> list = service.selectByProducTionTimeAndShift(productionTime,shift);
            return new Result(ResultCode.SUCCESS,list);
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;

   }

    /**
     * 检查工序
     */
    @RequestMapping(value = "/update", method = RequestMethod.PUT)
    public Result update(@RequestBody CheckTheProcess checkTheProcess) {
        //2.调用service更新
        service.update(checkTheProcess);
        return new Result(ResultCode.SUCCESS);
    }

    @RequestMapping(value = "/updateRemark", method = RequestMethod.PUT)
    public Result updateRemark(String id,String remark) {
        CheckTheProcess checkTheProcess = service.selectById(id);
        if(checkTheProcess!=null){
            checkTheProcess.setRemark(remark);
            checkTheProcess.setModifyDateTime(new Date());
            //2.调用service更新
            service.update(checkTheProcess);
        }
        return new Result(ResultCode.SUCCESS);
    }

    @RequestMapping(value = "/export", method = RequestMethod.GET)
    public void export(HttpServletResponse response, String productionTime, Integer shift) throws Exception {
        //1.构造数据
        List<CheckTheProcess> list = service.selectByProducTionTimeAndShift(productionTime,shift);
        //2.创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        String[] head1 = {"GTMC#4"};
        //3.构造sheet
        Sheet sheet = workbook.createSheet("检查工序1");
        //创建单元格样式对象
        CellStyle cellStyle = workbook.createCellStyle();
        //设置居中显示
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setBorderBottom(BorderStyle.MEDIUM);//下边框
        cellStyle.setBorderTop(BorderStyle.MEDIUM);//上边框
        cellStyle.setBorderLeft(BorderStyle.MEDIUM);//左边框
        cellStyle.setBorderRight(BorderStyle.MEDIUM);//有边框
        //设置单元格样式
        //设置边框
        //创建单元格样式对象
        CellStyle cellStyle1 = workbook.createCellStyle();
        cellStyle1.setBorderBottom(BorderStyle.NONE);//下边框
        cellStyle1.setBorderTop(BorderStyle.NONE);//上边框
        //设置字体
        Font font = workbook.createFont();//创建字体对象
        font.setFontName("宋体");//设置字体
        font.setFontHeightInPoints((short)16);//设置字号
        cellStyle1.setFont(font);
        Row row =sheet.createRow(0);
        AtomicInteger headersAi = new AtomicInteger();
        for (String title : head1) {
            Cell cell = row.createCell(headersAi.getAndIncrement());
            cell.setCellValue(title);
            cell.setCellStyle(cellStyle1);
        }
        //合并单元格
        CellRangeAddress region1 =new CellRangeAddress(0, 0, 0, 9);
        sheet.addMergedRegion(region1);
        AtomicInteger ai1 = new AtomicInteger(1);
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        Cell cell = null;
        Row row2 = sheet.createRow(ai1.getAndIncrement());
        cell = row2.createCell(0);
        cell.setCellValue("Date");
        //设置样式
        cell.setCellStyle(cellStyle);
        cell = row2.createCell(1);
        cell.setCellValue(productionTime);
        //设置样式
        cell.setCellStyle(cellStyle);
        sheet.setColumnWidth(1, 12 * 256);//设置第一列的宽度是31个字符宽度
        sheet.setColumnWidth(2, 12 * 256);//设置第一列的宽度是31个字符宽度
        sheet.setColumnWidth(3, 14 * 256);//设置第一列的宽度是31个字符宽度
        sheet.setColumnWidth(4, 16 * 256);//设置第一列的宽度是31个字符宽度
        sheet.setColumnWidth(9, 30 * 256);//设置第一列的宽度是31个字符宽度
        row2.setHeightInPoints(22);//设置行的高度是50个点
        AtomicInteger ai2 = new AtomicInteger(2);
        Row row3 = sheet.createRow(ai2.getAndIncrement());
        cell = row3.createCell(0);
        cell.setCellValue("Shift");
        //设置样式
        cell.setCellStyle(cellStyle);
        cell = row3.createCell(1);
        cell.setCellValue(shift);
        //设置样式
        cell.setCellStyle(cellStyle);
        //合并单元格
        CellRangeAddress region2 =new CellRangeAddress(1, 2, 2, 9);
        sheet.addMergedRegion(region2);
        // 合并单元格上边框样式
//        AtomicInteger ai3 = new AtomicInteger(3);
//        Row row4 = sheet.createRow(ai3.getAndIncrement());
//        cell = row4.createCell(0);
//        cell.setCellValue("");
//        //设置样式
//        cell.setCellStyle(cellStyle);
//
//        cell = row4.createCell(2);
//        cell.setCellValue("检查通过台数1");
//        //设置样式
//        cell.setCellStyle(cellStyle);
//        CellRangeAddress region3 =new CellRangeAddress(3, 3, 0, 1);
//        //合并单元格
//        sheet.addMergedRegion(region3);
//        CellRangeAddress region4 =new CellRangeAddress(3, 3, 2, 12);
//        //合并单元格
//        sheet.addMergedRegion(region4);

        AtomicInteger ai3 = new AtomicInteger(3);
        Row row4 = sheet.createRow(ai3.getAndIncrement());
        cell = row4.createCell(0);
        cell.setCellValue("编号");
        cell.setCellStyle(cellStyle);
        cell = row4.createCell(1);
        cell.setCellStyle(cellStyle);
        cell.setCellValue("时间带");
        cell = row4.createCell(2);
        cell.setCellValue("喷房设备故障");
        //设置样式
        cell.setCellStyle(cellStyle);
        cell = row4.createCell(3);
        cell.setCellValue("干燥炉设备故障");
        //设置样式
        cell.setCellStyle(cellStyle);
        cell = row4.createCell(4);
        cell.setCellValue("涂料温控设备故障");
        //设置样式
        cell.setCellStyle(cellStyle);
        cell = row4.createCell(5);
        cell.setCellValue("检查工序通过台数");
        //设置样式
        cell.setCellStyle(cellStyle);
        cell = row4.createCell(9);
        cell.setCellValue("特记事项");
        cell.setCellStyle(cellStyle);

        CellRangeAddress region5 =new CellRangeAddress(3, 4, 0, 0);
        //编号合并单元格
        sheet.addMergedRegion(region5);
        setCellBorder(sheet, region5);
        CellRangeAddress region6 =new CellRangeAddress(3, 4, 1, 1);
        //合并单元格
        sheet.addMergedRegion(region6);
        setCellBorder(sheet, region6);

        CellRangeAddress region7 =new CellRangeAddress(3, 4, 2, 2);
        //合并单元格
        sheet.addMergedRegion(region7);
        setCellBorder(sheet, region7);
        CellRangeAddress region9 =new CellRangeAddress(3, 4, 3, 3);
        //合并单元格
        sheet.addMergedRegion(region9);
        setCellBorder(sheet, region9);
        CellRangeAddress region10 =new CellRangeAddress(3, 4, 4, 4);
        //合并单元格
        sheet.addMergedRegion(region10);
        setCellBorder(sheet, region10);
        CellRangeAddress region11 =new CellRangeAddress(3, 3, 5, 8);
        //合并单元格
        sheet.addMergedRegion(region11);
        setCellBorder(sheet, region11);
        CellRangeAddress region12 =new CellRangeAddress(3, 4, 9, 9);
        //合并单元格
        sheet.addMergedRegion(region12);
        setCellBorder(sheet, region12);
        //3.构造sheet
        String[] titles = {"编号", "时间带", "喷房设备故障","干燥炉设备故障", "涂料温控设备故障","计划","实际","实际累积","可动率","特记事项"};
        Row row5 =sheet.createRow(4);
        AtomicInteger ai4 = new AtomicInteger();
        for (String title : titles) {
            cell = row5.createCell(ai4.getAndIncrement());
            cell.setCellValue(title);
            //设置样式
            cell.setCellStyle(cellStyle);
        }
        AtomicInteger datasAi = new AtomicInteger(5);
        int i = 1;
        Integer sprayEquipmentFailure = 0;//喷房设备故障
        Integer dryingOvenEquipmentFailure = 0;//干燥炉设备故障
        Integer coatingTemperatureControlEquipmentFailure = 0;//涂料温控设备故障
        Integer plan = 0;//计划
        Integer actual = 0;//实际
        Integer actualAccumlation = 0;//实际累积
        Integer movableRate = 0;//可动率
        if(list.size()>0){
            for (CheckTheProcess report : list) {
                sprayEquipmentFailure = sprayEquipmentFailure+report.getSprayEquipmentFailure();
                dryingOvenEquipmentFailure = dryingOvenEquipmentFailure+report.getDryingOvenEquipmentFailure();
                coatingTemperatureControlEquipmentFailure = coatingTemperatureControlEquipmentFailure + report.getCoatingTemperatureControlEquipmentFailure();
                plan = plan + report.getPlan();
                actual = actual+report.getActual();
                actualAccumlation = actualAccumlation+report.getActualAccumlation();
                movableRate = movableRate + report.getMovableRate();
                Row dataRow = sheet.createRow(datasAi.getAndIncrement());
                //编号
                cell = dataRow.createCell(0);
                cell.setCellValue(i++);
                cell.setCellStyle(cellStyle);
                //时间带
                cell = dataRow.createCell(1);
                cell.setCellValue(report.getTimeZone());
                cell.setCellStyle(cellStyle);
                //喷房设备故障
                cell = dataRow.createCell(2);
                cell.setCellValue(report.getSprayEquipmentFailure());
                cell.setCellStyle(cellStyle);
                //干燥炉设备故障
                cell = dataRow.createCell(3);
                cell.setCellValue(report.getDryingOvenEquipmentFailure());
                cell.setCellStyle(cellStyle);
                //涂料温控设备故障
                cell = dataRow.createCell(4);
                cell.setCellValue(report.getCoatingTemperatureControlEquipmentFailure());
                cell.setCellStyle(cellStyle);
                //计划
                cell = dataRow.createCell(5);
                cell.setCellValue(report.getPlan());
                cell.setCellStyle(cellStyle);
                //实际
                cell = dataRow.createCell(6);
                cell.setCellValue(report.getActual());
                cell.setCellStyle(cellStyle);
                //实际积累
                cell = dataRow.createCell(7);
                cell.setCellValue(report.getActualAccumlation());
                cell.setCellStyle(cellStyle);
                //可动率
                cell = dataRow.createCell(8);
                cell.setCellValue(report.getMovableRate());
                cell.setCellStyle(cellStyle);
                //特记事项
                cell = dataRow.createCell(9);
                cell.setCellValue(report.getRemark());
                cell.setCellStyle(cellStyle);
            }
            AtomicInteger ai6 = new AtomicInteger(list.size()+5);
            Row row6 = sheet.createRow(ai6.getAndIncrement());
            cell = row6.createCell(0);
            cell.setCellValue(" TOTAL");
            cell.setCellStyle(cellStyle);
            cell = row6.createCell(1);
            cell.setCellValue(0);
            cell.setCellStyle(cellStyle);
            cell = row6.createCell(2);
            cell.setCellValue(sprayEquipmentFailure);
            cell.setCellStyle(cellStyle);
            cell = row6.createCell(3);
            cell.setCellValue(dryingOvenEquipmentFailure);
            cell.setCellStyle(cellStyle);
            cell = row6.createCell(4);
            cell.setCellValue(coatingTemperatureControlEquipmentFailure);
            cell.setCellStyle(cellStyle);
            cell = row6.createCell(5);
            cell.setCellValue(plan);
            cell.setCellStyle(cellStyle);
            cell = row6.createCell(6);
            cell.setCellValue(actual);
            cell.setCellStyle(cellStyle);
            cell = row6.createCell(7);
            cell.setCellValue(actualAccumlation);
            cell.setCellStyle(cellStyle);
            cell.setCellStyle(cellStyle);
            cell = row6.createCell(8);
            cell.setCellValue(movableRate);
            cell.setCellStyle(cellStyle);
            cell = row6.createCell(9);
            cell.setCellValue("");
            cell.setCellStyle(cellStyle);
            CellRangeAddress region8 =new CellRangeAddress(list.size()+5, list.size()+5, 0, 1);
            //合并单元格
            sheet.addMergedRegion(region8);
        }

        String fileName = URLEncoder.encode(sheet.getSheetName()+".xlsx", "UTF-8");
        response.setContentType("application/octet-stream");
        response.setHeader("content-disposition", "attachment;filename=" + new
                String(fileName.getBytes("ISO8859-1")));
        response.setHeader("filename", fileName);
        workbook.write(response.getOutputStream());
    }

    @RequestMapping(value = "/export1", method = RequestMethod.GET)
    public void export1(HttpServletResponse response, String productionTime, Integer shift) throws Exception {
        //1.构造数据
        List<CheckTheProcess> list = service.selectByProducTionTimeAndShift(productionTime,shift);
        //2.创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        String[] head1 = {"GTMC#4"};
        //3.构造sheet
        Sheet sheet = workbook.createSheet("检查工序2");
        //创建单元格样式对象
        CellStyle cellStyle = workbook.createCellStyle();
        //设置居中显示
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setBorderBottom(BorderStyle.MEDIUM);//下边框
        cellStyle.setBorderTop(BorderStyle.MEDIUM);//上边框
        cellStyle.setBorderLeft(BorderStyle.MEDIUM);//左边框
        cellStyle.setBorderRight(BorderStyle.MEDIUM);//有边框
        //设置单元格样式
        //设置边框
        //创建单元格样式对象
        CellStyle cellStyle1 = workbook.createCellStyle();
        cellStyle1.setBorderBottom(BorderStyle.NONE);//下边框
        cellStyle1.setBorderTop(BorderStyle.NONE);//上边框
        //设置字体
        Font font = workbook.createFont();//创建字体对象
        font.setFontName("宋体");//设置字体
        font.setFontHeightInPoints((short)16);//设置字号
        cellStyle1.setFont(font);
        Row row =sheet.createRow(0);
        AtomicInteger headersAi = new AtomicInteger();
        for (String title : head1) {
            Cell cell = row.createCell(headersAi.getAndIncrement());
            cell.setCellValue(title);
            cell.setCellStyle(cellStyle1);
        }
        //合并单元格
        CellRangeAddress region1 =new CellRangeAddress(0, 0, 0, 9);
        sheet.addMergedRegion(region1);
        AtomicInteger ai1 = new AtomicInteger(1);
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        Cell cell = null;
        Row row2 = sheet.createRow(ai1.getAndIncrement());
        cell = row2.createCell(0);
        cell.setCellValue("Date");
        //设置样式
        cell.setCellStyle(cellStyle);
        cell = row2.createCell(1);
        cell.setCellValue(productionTime);
        //设置样式
        cell.setCellStyle(cellStyle);
        sheet.setColumnWidth(1, 12 * 256);//设置第一列的宽度是31个字符宽度
//        sheet.setColumnWidth(2, 12 * 256);//设置第一列的宽度是31个字符宽度
//        sheet.setColumnWidth(3, 14 * 256);//设置第一列的宽度是31个字符宽度
//        sheet.setColumnWidth(4, 16 * 256);//设置第一列的宽度是31个字符宽度
//        sheet.setColumnWidth(9, 30 * 256);//设置第一列的宽度是31个字符宽度
        row2.setHeightInPoints(22);//设置行的高度是50个点
        AtomicInteger ai2 = new AtomicInteger(2);
        Row row3 = sheet.createRow(ai2.getAndIncrement());
        cell = row3.createCell(0);
        cell.setCellValue("Shift");
        //设置样式
        cell.setCellStyle(cellStyle);
        cell = row3.createCell(1);
        cell.setCellValue(shift);
        //设置样式
        cell.setCellStyle(cellStyle);
        //合并单元格
        CellRangeAddress region2 =new CellRangeAddress(1, 2, 2, 9);
        sheet.addMergedRegion(region2);
        // 合并单元格上边框样式
//        AtomicInteger ai3 = new AtomicInteger(3);
//        Row row4 = sheet.createRow(ai3.getAndIncrement());
//        cell = row4.createCell(0);
//        cell.setCellValue("");
//        //设置样式
//        cell.setCellStyle(cellStyle);
//
//        cell = row4.createCell(2);
//        cell.setCellValue("检查通过台数1");
//        //设置样式
//        cell.setCellStyle(cellStyle);
//        CellRangeAddress region3 =new CellRangeAddress(3, 3, 0, 1);
//        //合并单元格
//        sheet.addMergedRegion(region3);
//        CellRangeAddress region4 =new CellRangeAddress(3, 3, 2, 12);
//        //合并单元格
//        sheet.addMergedRegion(region4);

        AtomicInteger ai3 = new AtomicInteger(3);
        Row row4 = sheet.createRow(ai3.getAndIncrement());
        cell = row4.createCell(0);
        cell.setCellValue("编号");
        cell.setCellStyle(cellStyle);
        cell = row4.createCell(1);
        cell.setCellStyle(cellStyle);
        cell.setCellValue("时间带");
        cell = row4.createCell(2);
        cell.setCellValue("检查工序");
        cell.setCellStyle(cellStyle);
        cell = row4.createCell(8);
        cell.setCellValue("计划停止");
        cell.setCellStyle(cellStyle);
        cell = row4.createCell(9);
        cell.setCellValue("ANDON入");
        cell.setCellStyle(cellStyle);



        CellRangeAddress region5 =new CellRangeAddress(3, 4, 0, 0);
        //编号合并单元格
        sheet.addMergedRegion(region5);
        setCellBorder(sheet, region5);
        CellRangeAddress region6 =new CellRangeAddress(3, 4, 1, 1);
        //合并单元格
        sheet.addMergedRegion(region6);
        setCellBorder(sheet, region6);

        CellRangeAddress region7 =new CellRangeAddress(3, 3, 2, 7);
        //合并单元格
        sheet.addMergedRegion(region7);
        setCellBorder(sheet, region7);
        CellRangeAddress region9 =new CellRangeAddress(3, 4, 8, 8);
        //合并单元格
        sheet.addMergedRegion(region9);
        setCellBorder(sheet, region9);
        CellRangeAddress region10 =new CellRangeAddress(3, 4, 9, 9);
        //合并单元格
        sheet.addMergedRegion(region10);
        setCellBorder(sheet, region10);
        //3.构造sheet
        String[] titles = {"编号", "时间带", "欠车","满车", "检查1延迟","检查2延迟","检查3延迟","设备异常","计划停止","ANDON入"};
        Row row5 =sheet.createRow(4);
        AtomicInteger ai4 = new AtomicInteger();
        for (String title : titles) {
            cell = row5.createCell(ai4.getAndIncrement());
            cell.setCellValue(title);
            //设置样式
            cell.setCellStyle(cellStyle);
        }
        AtomicInteger datasAi = new AtomicInteger(5);
        int i = 1;
        Integer oweCar = 0;//欠车
        Integer fullCar = 0;//满车
        Integer checkDelay1 = 0;//检查延迟1
        Integer checkDelay2 = 0;//检查延迟2
        Integer checkDelay3 = 0;//检查延迟3
        Integer equipmentAbnormal = 0;//设备异常
        Integer planStop = 0;//计划停止
        Integer andonIn = 0;//ANDON入
        if(list.size()>0){
            for (CheckTheProcess report : list) {
                oweCar = oweCar+report.getOweCar();
                fullCar = fullCar+report.getFullCar();
                checkDelay1 = checkDelay1 + report.getCheckDelay1();
                checkDelay2 = checkDelay2 + report.getCheckDelay2();
                checkDelay3 = checkDelay3+report.getCheckDelay3();
                equipmentAbnormal = equipmentAbnormal+report.getEquipmentAbnormal();
                planStop = planStop + report.getPlanStop();
                andonIn = andonIn + report.getAndonIn();
                Row dataRow = sheet.createRow(datasAi.getAndIncrement());
                //编号
                cell = dataRow.createCell(0);
                cell.setCellValue(i++);
                cell.setCellStyle(cellStyle);
                //时间带
                cell = dataRow.createCell(1);
                cell.setCellValue(report.getTimeZone());
                cell.setCellStyle(cellStyle);
                //欠车
                cell = dataRow.createCell(2);
                cell.setCellValue(report.getOweCar());
                cell.setCellStyle(cellStyle);
                //满车
                cell = dataRow.createCell(3);
                cell.setCellValue(report.getFullCar());
                cell.setCellStyle(cellStyle);
                //检查延迟1
                cell = dataRow.createCell(4);
                cell.setCellValue(report.getCheckDelay1());
                cell.setCellStyle(cellStyle);
                //检查延迟2
                cell = dataRow.createCell(5);
                cell.setCellValue(report.getCheckDelay2());
                cell.setCellStyle(cellStyle);
                //检查延迟3
                cell = dataRow.createCell(6);
                cell.setCellValue(report.getCheckDelay3());
                cell.setCellStyle(cellStyle);
                //设备异常
                cell = dataRow.createCell(7);
                cell.setCellValue(report.getEquipmentAbnormal());
                cell.setCellStyle(cellStyle);
                //计划停止
                cell = dataRow.createCell(8);
                cell.setCellValue(report.getPlanStop());
                cell.setCellStyle(cellStyle);
                //ANDON入
                cell = dataRow.createCell(9);
                cell.setCellValue(report.getAndonIn());
                cell.setCellStyle(cellStyle);
            }
            AtomicInteger ai6 = new AtomicInteger(list.size()+5);
            Row row6 = sheet.createRow(ai6.getAndIncrement());
            cell = row6.createCell(0);
            cell.setCellValue(" TOTAL");
            cell.setCellStyle(cellStyle);
            cell = row6.createCell(1);
            cell.setCellValue(0);
            cell.setCellStyle(cellStyle);
            cell = row6.createCell(2);
            cell.setCellValue(oweCar);
            cell.setCellStyle(cellStyle);
            cell = row6.createCell(3);
            cell.setCellValue(fullCar);
            cell.setCellStyle(cellStyle);
            cell = row6.createCell(4);
            cell.setCellValue(checkDelay1);
            cell.setCellStyle(cellStyle);
            cell = row6.createCell(5);
            cell.setCellValue(checkDelay2);
            cell.setCellStyle(cellStyle);
            cell = row6.createCell(6);
            cell.setCellValue(checkDelay3);
            cell.setCellStyle(cellStyle);
            cell = row6.createCell(7);
            cell.setCellValue(equipmentAbnormal);
            cell.setCellStyle(cellStyle);
            cell.setCellStyle(cellStyle);
            cell = row6.createCell(8);
            cell.setCellValue(planStop);
            cell.setCellStyle(cellStyle);
            cell = row6.createCell(9);
            cell.setCellValue(andonIn);
            cell.setCellStyle(cellStyle);
            CellRangeAddress region8 =new CellRangeAddress(list.size()+5, list.size()+5, 0, 1);
            //合并单元格
            sheet.addMergedRegion(region8);
        }

        String fileName = URLEncoder.encode(sheet.getSheetName()+".xlsx", "UTF-8");
        response.setContentType("application/octet-stream");
        response.setHeader("content-disposition", "attachment;filename=" + new
                String(fileName.getBytes("ISO8859-1")));
        response.setHeader("filename", fileName);
        workbook.write(response.getOutputStream());
    }
    //合并单元格样式
    private void setCellBorder(Sheet sheet, CellRangeAddress cellAddresses) {
        RegionUtil.setBorderTop(BorderStyle.MEDIUM, cellAddresses, sheet);
        RegionUtil.setBorderBottom(BorderStyle.MEDIUM, cellAddresses, sheet);
        RegionUtil.setBorderLeft(BorderStyle.MEDIUM, cellAddresses, sheet);
        RegionUtil.setBorderRight(BorderStyle.MEDIUM, cellAddresses, sheet);


    }


}
